package ex;

import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.*;
import java.sql.Statement;


/**
 * Servlet implementation class for Servlet: Ex21
 *
 */
 public class Ex21 extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {

	PrintWriter out;
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doMenu(request, response);
	}  	
	
	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getParameter("retrieve") != null) doRetrieve(request, response);
		else if (request.getParameter("insert") != null || request.getParameter("name") != null) doInsert(request, response);
		else if (request.getParameter("delall") != null) doDeleteAll(request, response);
		else doDeleteOrUpdate(request,response);
	}   
	
	protected void doMenu(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		out = response.getWriter();
		out.print("<html><body style=\"font-family: arial\"><b>Welcome</b>");
		
		out.print("<br/><form method=POST>");
		out.print("<input type=\"submit\" name=\"insert\" value=\"Insert\"/>");
		out.print("<br/><input type=\"submit\" name=\"retrieve\" value=\"Retrieve\"/>");
		out.print("<br/><input type=\"submit\" name=\"delall\" value=\"Clear database\"/>");
		out.print("</form>");
		
		out.print("</body></html>");
	}
	
	protected void doRetrieve(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		String s = null;
		String s0 = null;
		String s1 = null;
		
		out = response.getWriter();
		out.print("<html><body style=\"font-family: arial\">");
		out.print("<form method=POST>");
		try {

			/** Loading the driver*/
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			/** Getting Connection*/
			Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");

			/** Creating Statement*/
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery("select * from student");

			while(rs.next()) {
				s = rs.getString(1);
				s0 = rs.getString(2);
				s1 = rs.getString(3);
				
				out.println("<br/>Name: " + s);
				out.println("Address: " + s0);
				out.println("Contact #: " + s1);
				out.print("&nbsp<input type=\"submit\" name=\"del" + s +  "\" value=\"delete\"/>");
				out.print("&nbsp<input type=\"submit\" name=\"upd" + s +  "\" value=\"update\"/>");
			}


			/** Closing the Connection*/
			stmt.close();
			con.close();
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			out.print("</form>");
			out.print("<br/><br/><a href=\"http://localhost:8080/ex21/Ex21\">back</a></body></html>");
		}
	}
	
	protected void doInsert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		out = response.getWriter();
		out.print("<html><body style=\"font-family: arial\">");
		out.print("<form method=POST><b>Name:</b>&nbsp<input type=\"text\" name=\"name\"/><br/><b>Address:</b>&nbsp<input type=\"text\" name=\"address\"><br/><b>Phone:</b>&nbsp<input type=\"text\" name=\"phone\">"
				+ "<br/><input type=\"submit\" value=\"Insert\"/><input type=\"reset\"/></form>"		
				);
		if (request.getParameter("name") != null )
		{
			String name = request.getParameter("name");
			String address = request.getParameter("address");
			String phone = request.getParameter("phone");
			if (request.getParameter("name") != ""){
			try {

				/** Loading the driver*/
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

				/** Getting Connection*/
				Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");
				con.setAutoCommit(false);
				PreparedStatement stmnt = con.prepareStatement("insert into student(name,address,phone) values(?,?,?)");
				stmnt.setString(1,name);
				stmnt.setString(2,address);
				stmnt.setString(3,phone);
				int ex = stmnt.executeUpdate();
				
				
				con.commit();
				out.print("<br/>insert success!");
				
				stmnt.close();
				con.close();
			} 
			catch (Exception e) {
				e.printStackTrace();
			}
			}
			else out.print("<br/>blank fields.");
		}


		
		out.print("<br/><br/><a href=\"http://localhost:8080/ex21/Ex21\">back</a></body></html>");
		
	}
	
	protected void doDeleteOrUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		boolean f = false;
		boolean f0 = false;
		String s = null;
		String s1 = null;
		String s2 = null;
		
		out = response.getWriter();
		out.print("<html><body style=\"font-family: arial\">");
		
		try {

			//Loading the driver
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			// Getting Connection
			Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");

			// Creating Statement
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery("select * from student");
			while(rs.next()) {
				s = rs.getString(1);
				s1 = rs.getString(2);
				s2 = rs.getString(3);
				if (request.getParameter("del" + s) != null)
				{
					f = true;
					break;
				}
				
				if (request.getParameter("upd" + s) != null)
				{
					f0 = true;
					break;
				}
			}
			
			PreparedStatement st;
			
			if (f)
			{

				st = con.prepareStatement("delete from student where name=?");
				st.setString(1, s);
				int ex = st.executeUpdate();
				out.print("delete success!");

				st.close();
				
			}
			
			if (f0)
			{
				out.print("<form method=POST>");
				out.print("<b>Name:</b>&nbsp<input type=\"text\" name=\"uname\" value=\"" + s + "\" /><br/>");
				out.print("<b>Address:</b>&nbsp<input type=\"text\" name=\"uaddress\" value=\"" + s1 + "\" /><br/>");
				out.print("<b>Phone:</b>&nbsp<input type=\"text\" name=\"uphone\" value=\"" + s2 + "\" /><br/>");
				out.print("<input type=\"hidden\" name=\"hiddentext\" value=\"" + s + "\" /><br/>");
				out.print("<input type=\"submit\" name=\"update\"/><br/>");
				
				out.print("</form>");
				

			}
			
			if (request.getParameter("update") != null)
			{
				String t0 = request.getParameter("uname");
				String t1 = request.getParameter("uaddress");
				String t2 = request.getParameter("uphone");
				String t4 = request.getParameter("hiddentext");
				
				st = con.prepareStatement("update student set name=?, address=?, phone=? where name=?");
				st.setString(1, t0);
				st.setString(2, t1);
				st.setString(3, t2);
				st.setString(4, t4);
				int ex = st.executeUpdate();
				out.print("update success!");

				st.close();
				
			}


			
			stmt.close();
			con.close();
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			out.print("<br/><br/><a href=\"http://localhost:8080/ex21/Ex21\">back</a></body></html>");;
		}
	}
	
	protected void doDeleteAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		try {

			/** Loading the driver*/
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			/** Getting Connection*/
			Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");

			/** Creating Statement*/
			
			PreparedStatement stmt = con.prepareStatement("delete * from student");
			int ex = stmt.executeUpdate();


			/** Closing the Connection*/
			stmt.close();
			con.close();
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			doRetrieve(request, response);
		}
		
	}
}